import re
import pymssql

class PoManager(object):
	def __init__(self, fn):
		self.fn = fn

	def open_conn(self):
		self.conn = pymssql.connect(host='10.63.2.60', 
			user='eerpsa', password='P@ssword', database='cmape')

	def close_conn(self):
		self.conn.close()

	def read_pos(self):
		data = ""
		with open(self.fn, "r") as f:
			data = f.read()

		arr = re.split(r"\n", data)
		polist = set()
		for item in arr:
			polist.add(item)
		return polist


	def check_po_exists(self, po):
		cursor = self.conn.cursor()
		cursor.execute("SELECT COUNT(*) from dbo.T_APP_ApplicationBase WHERE SAPNumber = '%s'" % po)
		cnt = cursor.fetchone()[0]
		cursor.close()
		if cnt > 0:
			print("find po >", po)
		return cnt > 0

	def find_by_po(self, po):
		cursor = self.conn.cursor()
		cursor.execute("SELECT * from dbo.T_APP_ApplicationBase WHERE SAPNumber = '%s'" % po)
		info = cursor.fetchone()
		return info

	def find_po_in_eerp(self):
		ret = []
		try:
			po_list = self.read_pos()
			s = ""
			for po in po_list:
				info = self.find_by_po(po)
				if info:
					ret.append(info)
					print(info)
					# s  = s + po + "\n"

			# with open("exists_po.txt", "w") as f:
			# 	f.write(s)

			print("len >", len(ret))

		except:
			print("error")

		return ret

	def save_as_csv(self, pos):
		s = ""
		for po in pos:
			s = s + po[3] + "," + po[6] + "," + po[7] + "\n"

		with open("exists_po.csv", "w") as f:
				f.write(s)





# check_po("4500183077")


fn = r"C:\Users\GJ\Desktop\pos.txt"

po_mgr = PoManager(fn)
po_mgr.open_conn()
pos = po_mgr.find_po_in_eerp()
po_mgr.save_as_csv(pos)
po_mgr.close_conn()









